#!/bin/bash

#
#  @auther: deep as the sea
#  @qq: 83544844
#  @wx: doit_edu
#  @date: 2021-09-21
#  @des: 设备账号动态绑定评分表计算任务
#
#

export HIVE_HOME=/opt/apps/hive

dt=`date -d'-1 day' +%Y-%m-%d`


if [ $# -gt 0 ];then
dt=$1
fi

pre=`date -d"-1 day ${dt}" +%Y-%m-%d`


echo "INFO:  设备账号动态绑定评分表计算任务开始，评分表(${pre}) + 日志表(${dt})  => 评分表(${dt})"

${HIVE_HOME}/bin/hive -e "
WITH cur AS(
SELECT
  deviceid                         as device_id,
  if(account='',null,account)      as account,
  count(distinct sessionid) * 10   as bind_score,
  max(\`timestamp\`)                 as last_login
FROM ods.mall_app_action_log
WHERE dt='${dt}'
GROUP BY deviceid,if(account='',null,account)
)
,his AS(
SELECT
  device_id,
  account,
  bind_score,
  last_login
FROM dws.mall_app_dvc_bds 
WHERE dt='${pre}'

)

INSERT INTO TABLE dws.mall_app_dvc_bds PARTITION(dt = '${dt}')
SELECT
  nvl(cur.device_id,his.device_id) as device_id,
  nvl(cur.account,his.account) as account,
  nvl(cur.last_login,his.last_login) as last_login,
  CASE
    WHEN cur.account is not null and his.account is not null then cur.bind_score + his.bind_score
    WHEN cur.account is not null and his.account is null then cur.bind_score
    else nvl(his.bind_score*0.5,0)
  END as bind_score
FROM  cur FULL JOIN his
ON cur.device_id = his.device_id AND cur.account = his.account
"

if [ $? -eq 0 ];then
echo "dw task sucessed :设备账号动态绑定评分表计算任务执行成功，评分表(${pre}) + 日志表(${dt})  => 评分表(${dt})" | mail -s '数易平台任务运行成功通知' 83544844@qq.com
else
echo "dw task failed :设备账号动态绑定评分表计算任务执行失败，评分表(${pre}) + 日志表(${dt})  => 评分表(${dt})" | mail -s '数易平台任务运行失败通知' 83544844@qq.com
fi

